#!/usr/local/bin/python
# -*- coding: utf-8 -*-



from sqlalchemy import Column, Integer, Float, and_, Text, or_
from sqlalchemy.orm import declarative_base

import init
from init import session


Base = declarative_base()

#Base = declarative_base()

class AccountBookAlchemy(Base):
    '''
    类功能：
    '''

    #__tablename__ = "account_book_temp"
    __tablename__ = "account_book"

    id = Column(Integer, primary_key=True, autoincrement=True, comment="")
    year = Column(Integer, comment="")
    month = Column(Integer, comment="")
    account_type = Column(Text, comment="")    #property or ownership
    subject_name_0 = Column(Text, comment="")
    subject_name_1 = Column(Text, comment="")
    subject_name_2 = Column(Text, comment="")
    subject_name_3 = Column(Text, comment="")
    subject_name_4 = Column(Text, comment="")
    subject_name_5 = Column(Text, comment="")
    subject_name = Column(Text, comment="")
    start_value = Column(Float, comment="")
    increase_value = Column(Float, comment="")
    decrease_value = Column(Float, comment="")
    end_value = Column(Float, comment="")

    def __init__(self,
                 year=2022,
                 month=1,
                 accountType="property",
                 subjectName_0="",
                 subjectName_1="",
                 subjectName_2="",
                 subjectName_3="",
                 subjectName_4="",
                 subjectName_5="",
                 subjectName="",
                 startValue=0.0,
                 increaseValue=0.0,
                 decreaseValue=0.0,
                 endValue=0.0
                 ):
        self.year = year
        self.month = month
        self.account_type = accountType
        self.subject_name_0 = subjectName_0
        self.subject_name_1 = subjectName_1
        self.subject_name_2 = subjectName_2
        self.subject_name_3 = subjectName_3
        self.subject_name_4 = subjectName_4
        self.subject_name_5 = subjectName_5
        self.subject_name = subjectName
        self.start_value = startValue
        self.increase_value = increaseValue
        self.decrease_value = decreaseValue
        self.end_value = endValue

    def AddAccountBookItem(self):
        '''
        函数功能：添加一条数据
        :return:
        '''
        session.add(self)
        session.commit()

    def UpdateAccountBookItem(self):
        '''
        函数功能；更新一条数据
        :return:
        '''

    def SearchAccountBookByMonth(self, year, month):
        '''
        函数功能：按照条件查询数据
        :param year:
        :param month:
        :return:
        '''

        accountBookInfo = session.query(AccountBookAlchemy).filter(and_(AccountBookAlchemy.year == year,
                                                                        AccountBookAlchemy.month == month)).all()

        return accountBookInfo

    def SearchAccountBookByMonthSub(self, year, month, subjectPath):
        '''
        函数功能：基于十时间和科目路径前缀，
        :param year:
        :param month:
        :param subjectPath:
        :return:
        '''

        likeCondition = subjectPath+"%"
        accountBookInfo = session.query(AccountBookAlchemy).filter(and_(AccountBookAlchemy.year == year,
                                                                        AccountBookAlchemy.month == month,))\
                                                           .filter(AccountBookAlchemy.subject_name.like(likeCondition)).all()
        return accountBookInfo

    def ShowData(self):
        '''
        函数功能：展示读取出来的数据
        :return:
        '''
        pass

    def DelAccountBookByMonth(self, year, month):
        '''
        函数功能，删除指定月份的张博数据
        :param year:
        :param month:
        :return:
        '''

        deleteAccountBookInfo = session.query(AccountBookAlchemy).filter(and_(AccountBookAlchemy.year==year,
                                                                     AccountBookAlchemy.month==month))
        deleteAccountBookInfo.delete()
        session.commit()

    def GetDebtDateByYear(self, year):
        '''
        函数功能：获取指定年份各月的负债信息
        :param year:
        :return:
        '''


        accountBookAlchemy = session.query(AccountBookAlchemy)\
                                     .filter(AccountBookAlchemy.year==year)  \
                                     .filter(or_(AccountBookAlchemy.subject_name_2=="信用卡",  \
                                                 AccountBookAlchemy.subject_name_2=="蚂蚁花呗"))  \
                                     .order_by(AccountBookAlchemy.month)  \
                                     .all()
        return accountBookAlchemy


def main():
    accountBookAlchemy = AccountBookAlchemy(year =2022,
                                          month = 7,
                                          accountType="property",
                                          subjectName_0="世界",
                                          subjectName_1="中国",
                                          subjectName_2="北京",
                                          subjectName_3="海淀",
                                          subjectName_4="中关村",
                                          subjectName_5="人大",
                                          subjectName="及爱家",
                                          startValue=0.0,
                                          increaseValue=0.0,
                                          decreaseValue=0.0,
                                          endValue=0.0
                                          )

    accountBookAlchemy.AddAccountBookItem()
    '''
        def __init__(self,
                 year=2022,
                 month=1,
                 accountType="property",
                 subjectName_0="",
                 subjectName_1="",
                 subjectName_2="",
                 subjectName_3="",
                 subjectName_4="",
                 subjectName_5="",
                 subjectName="",
                 startValue=0.0,
                 increaseValue=0.0,
                 decreaseValue=0.0,
                 endValue=0.0
                 ):
    '''
#创建数据库表
AccountBookAlchemy.metadata.create_all(init.engine)
if __name__ == '__main__':
    main()